# 脚本功能:从teambition同步数据到sheet中
import requests
import json
from datetime import datetime
# sheetname defination
tb_org_sheetname = "TB数据"
config_sheetname = "配置"
df = dbt(sheet_name=tb_org_sheetname)
task_status_map = {
    "60418b08c679e20044c09c0c": "其他功能解决",
    "64af9ca33bb6c5ae1239f98e": "待开发评估",
    "654da2f56c338454318df4de": "待发布SIT",
    "5e0dabc57d161c00217800b8": "开发中",
    "6604cf0816117c37565adb54": "待开发",
    "5e0db98a8fc7b60022d301c6": "待组内评审",
    "6535cfab96d44880e05a51b1": "待测试",
    "6535cfab96d44880e05a51b4": "测试中",
    "5f40cf2267d6500044ec1a29": "待分析",
    "6535cfab96d44880e05a51b7": "已发布SIT",
    "5e0dabc57d161c00217800b9": "已发布生产",
    "614407e924ae43003fd05019": "已完成（不发版）",
    "603ddbf166272e0044b3d934": "已发布UAT",
    "5e0dabc57d161c00217800b7": "待产品内审",
    "5e0db97739a39700233b0199": "待审阅",
    "6340edfabb167700408b7339": "经评估无法实现",
    "65e80e978ec65144610baca1": "待架构组评审",
}
tags_map = {
    "delay": "668260863fa78a80a1fcbc45",
    "parent_task": "6729ecdb5e2799bcccc4e113",
    "hotfix": "5f60a1601ca5a400443ae514",
}
# fetch task data from Teambition


def fetch_data(sprint_name):
    result = []
    res = requests.get(
        "https://quality-tools.maycur.com//teambition_script/sprint/task/?name="+sprint_name)
    if res.status_code == 200:
        result = res.json()["data"]["results"]
    return result
# transform the data construct for this wps table


def data_transform(fb_task):
    task = {}
    task["task_id"] = fb_task["id"]
    task["task_no"] = "DB-" + str(fb_task["uniqueId"])
    task["task_status"] = task_status_map.get(str(fb_task["tfsId"]), "未知状态")
    task["sprint_name"] = fb_task["sprintName"]
    task["estimate_workload"] = 0
    task["title"] = fb_task["content"]
    task["biz_team"] = "未定义"
    task["task_type"] = "未定义"
    task["frontend"] = "未定义"
    task["backend"] = "未定义"
    task["test"] = "未定义"
    task["main_customer"] = "未定义"
    task["task_type_full"] = "未定义"
    task["estimate_backend_day"] = 0
    task["estimate_frontend_day"] = 0
    task["estimate_test_day"] = 0
    task["real_backend_day"] = 0
    task["real_frontend_day"] = 0
    task["real_test_day"] = 0
    task["isDelay"] = tags_map["delay"] in fb_task["tagIds"]
    task["hotfix"] = tags_map["hotfix"] in fb_task["tagIds"]
    task["hotfix_date"] = None
    for cf in fb_task["customfields"]:
        if len(cf["value"]) == 0:
            continue
        # 主责客户
        if cf["cfId"] == "61123175d7d6065f66ee29e7":
            task["main_customer"] = cf["value"][0]["title"]
        # 需求分类
        elif cf["cfId"] == "5ce4d1abb644a600011cf268":
            task["task_type_original"] = cf["value"][0]["title"]
            task["task_type_full"] = json.loads(
                cf["value"][0]["metaString"])["allTitle"]
            if "客成" in task["task_type_full"]:
                task["task_type"] = "客成需求"
            elif "交付" in task["task_type_full"]:
                task["task_type"] = "交付需求"
            elif "产品规划" in task["task_type_full"]:
                task["task_type"] = "产品规划"
            elif "技术规划" in task["task_type_full"]:
                task["task_type"] = "技术规划"
            elif "质量保障" in task["task_type_full"]:
                task["task_type"] = "质量保障"
            elif "用户体验" in task["task_type_full"]:
                task["task_type"] = "用户体验"
            else:
                task["task_type"] = "内部需求"
        elif cf["cfId"] == "61810ffc967570acea7feabd":
            task["estimate_workload"] = float(cf["value"][0]["title"]) or 0
        elif cf["cfId"] == "5f93e91719e65b9393474e07":
            task["biz_team"] = cf["value"][0]["title"]
        elif cf["cfId"] == "660be84c797f388d73107b34":
            task["frontend"] = cf["value"][0]["title"]
        elif cf["cfId"] == "660be86cfdae364d32426542":
            task["backend"] = cf["value"][0]["title"]
        elif cf["cfId"] == "660be8a675863a6977f65356":
            task["test"] = cf["value"][0]["title"]
        elif cf["cfId"] == "600e37e3e633856d93e1f21a":
            task["estimate_frontend_day"] = cf["value"][0]["title"]
        elif cf["cfId"] == "600e3802727d8e77e7e72ee3":
            task["estimate_backend_day"] = cf["value"][0]["title"]
        elif cf["cfId"] == "600e3810f38ec6065a90093d":
            task["estimate_test_day"] = cf["value"][0]["title"]
        elif cf["cfId"] == "62de3ebad7ab965fbdf6c144":
            task["real_frontend_day"] = cf["value"][0]["title"]
        elif cf["cfId"] == "62de3ed2d51a977b23f42b6b":
            task["real_backend_day"] = cf["value"][0]["title"]
        elif cf["cfId"] == "62de3ee92d8f775082f164f8":
            task["real_test_day"] = cf["value"][0]["title"]
        elif cf["cfId"] == "68d3684603557d40824a0aad":
            hotfix_date = cf["value"][0]["title"]
            # task["hotfix_date"] = hotfix_date
            task["hotfix_date"] = datetime.strptime(
                hotfix_date, "%Y-%m-%dT%H:%M:%S.%fZ").strftime("%Y-%m-%d") if hotfix_date else None
    return task

# generate new table data


def generateTableData(data):
    result = []
    for v in data:
        item = {}
        item["标题"] = v["title"]
        item["任务ID"] = v["task_no"]
        item["负责小组"] = v["biz_team"]
        item["需求分类"] = v["task_type_full"]
        item["迭代"] = v["sprint_name"]
        item["后端预估人天"] = v["estimate_backend_day"]
        item["前端预估人天"] = v["estimate_frontend_day"]
        item["测试预估人天"] = v["estimate_test_day"]
        item["后端人天"] = v["real_backend_day"]
        item["前端人天"] = v["real_frontend_day"]
        item["测试人天"] = v["real_test_day"]
        item["org_backend_days"] = v["real_backend_day"]
        item["org_frontend_days"] = v["real_frontend_day"]
        item["org_test_days"] = v["real_test_day"]
        item["产研预估人天"] = v["estimate_workload"]
        item["是否延期"] = "是" if v["isDelay"] else "否"
        item["主责客户"] = v["main_customer"]
        item["需求类型"] = v["task_type"]
        item["tb_id"] = v["task_id"]
        item["任务状态"] = v["task_status"]
        item["hotfix"] = "是" if v["hotfix"] else "否"
        item["hotfix时间"] = v["hotfix_date"]
        result.append(item)
    return result
# generate updated table data


def generateUpdateTableData(data, existedData, sprint_list):
    result = []
    sheet_sprints_tbids = []
    current_sprints_tbids = []
    for v in existedData:
        if v["迭代"] in sprint_list:
            sheet_sprints_tbids.append(v["tb_id"])
    for v in data:
        current_sprints_tbids.append(v["task_id"])
        item = {}
        for d in existedData:
            if v["task_id"] == d["tb_id"]:
                item["_rid"] = d["_rid"]
                if d["测试人天"] == 0 or d["测试人天"] > float(v["real_test_day"]):
                    item["测试人天"] = v["real_test_day"]
                if d["后端人天"] == 0 or d["后端人天"] > float(v["real_backend_day"]):
                    item["后端人天"] = v["real_backend_day"]
                if d["前端人天"] == 0 or d["前端人天"] > float(v["real_frontend_day"]):
                    item["前端人天"] = v["real_frontend_day"]
                break
        item["标题"] = v["title"]
        item["任务ID"] = v["task_no"]
        item["负责小组"] = v["biz_team"]
        item["需求分类"] = v["task_type_full"]
        item["迭代"] = v["sprint_name"]
        item["后端预估人天"] = v["estimate_backend_day"]
        item["前端预估人天"] = v["estimate_frontend_day"]
        item["测试预估人天"] = v["estimate_test_day"]
        item["org_backend_days"] = v["real_backend_day"]
        item["org_frontend_days"] = v["real_frontend_day"]
        item["org_test_days"] = v["real_test_day"]
        item["产研预估人天"] = v["estimate_workload"]
        item["是否延期"] = "是" if v["isDelay"] else "否"
        item["hotfix"] = "是" if v["hotfix"] else "否"
        item["主责客户"] = v["main_customer"]
        item["需求类型"] = v["task_type"]
        item["任务状态"] = v["task_status"]
        item["hotfix时间"] = v["hotfix_date"]
        if item.get("_rid"):
            result.append(item)
    difference_tbids = list(set(sheet_sprints_tbids) -
                            set(current_sprints_tbids))
    print(difference_tbids)
    for tb_id in difference_tbids:
        item = {}
        for d in existedData:
            if tb_id == d["tb_id"]:
                item["_rid"] = d["_rid"]
                break
        if item.get("_rid"):
            item["迭代"] = "迭代变更"
            result.append(item)
    return result
# 获取需要移除的迭代任务数据
# def getRemovedTasks(data,existedData):
#   sprint_taskids=[]
#   removed_taskid=[]
#   for d in data:
#     sprint_taskids.append(d["task_id"])
#   for d in existedData:
#     if d["tb_id"] in sprint_taskids:
#       removed_taskid.append(d["tb_id"])
#   return removed_taskid


def getExistedTasks():
    tbid_list = []
    tbrid_list = []
    df = dbt(sheet_name=tb_org_sheetname)
    for i, d in df.iterrows():
        tbid_list.append(d["tb_id"])
        tbrid_list.append(
            {"_rid": i, "tb_id": d["tb_id"], "后端人天": d["后端人天"], "前端人天": d["前端人天"], "测试人天": d["测试人天"], "迭代": d["迭代"]})
    return tbid_list, tbrid_list

######## divider########
######## -main-#########


def main():
    config_df = dbt(sheet_name=config_sheetname)
    sprint_list = []
    for v in config_df.itertuples():
        _id, actionName, sprints = v
        if actionName == "自动流程数据同步":
            sprint_list = sprints.strip('[]').split()

    if len(sprint_list) > 0:
        tb_data = []
        insert_tb_data = []
        update_tb_data = []
        original_tb_data = []
        tb_exist_ids, tb_exist_rids = getExistedTasks()
        # fetch sprints data
        for sn in sprint_list:
            original_tb_data.extend(fetch_data(sn))
        for v in original_tb_data:
            # transform the original data into readable data
            task_data = data_transform(v)
            if task_data["biz_team"] != "报表组":
                tb_data.append(task_data)
        # create update&insert data
        for d in tb_data:
            if d["task_id"] in tb_exist_ids:
                update_tb_data.append(d)
            else:
                insert_tb_data.append(d)
        if len(update_tb_data) > 0:
            update_dbt(generateUpdateTableData(
                update_tb_data, tb_exist_rids, sprint_list), sheet_name=tb_org_sheetname)
            print("本次更新数据"+str(len(update_tb_data))+"条")
        if len(insert_tb_data) > 0:
            insert_dbt(generateTableData(insert_tb_data),
                       sheet_name=tb_org_sheetname)
            print("本次新增数据"+str(len(insert_tb_data))+"条")


######## execute#########
main()
